﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
using System.Threading.Tasks;

namespace DBHelpers
{
    public class DataHelper
    {
        /// <summary>
        /// 执行sql语句，返回DataTale
        /// </summary>
        /// <param name="crmSqlConnect">连接字符串</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryDatable(string crmSqlConnect, string sql, SqlParameter[] sqlParameters = null)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(crmSqlConnect))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 0;
                        if (sqlParameters != null) cmd.Parameters.AddRange(sqlParameters);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                    }

                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }

        /// <summary>
        /// 执行添加删除修改语句
        /// </summary>
        /// <param name="connstr"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string PerformDeleUpdateAdd(string connstr, string sql)
        {
            SqlConnection conn = null;
            int row = 0;
            try
            {
                conn = new SqlConnection(connstr);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                row = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return "sql执行错误信息：" + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return row.ToString();
        }

        /// <summary>
        /// 将数据库读出的数据转换为DataTable为型集合
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public DataTable ConverDataReaderToDataTable(IDataReader reader)
        {
            if (reader == null) return null;
            //实例化一个DataTable
            DataTable dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;

            int fieldCount = reader.FieldCount;
            //在表中创建字段
            for (int i = 0; i < fieldCount; i++)
            {
                dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            //创建行
            while (reader.Read())
            {
                var row = dataTable.NewRow();
                for (int i = 0; i < fieldCount; i++)
                {
                    if (DBNull.Value == reader[i]) continue;
                    row[i] = reader[i].ToString();
                }
                dataTable.Rows.Add(row);
            }
            return dataTable;
        }
    }
}
